package com.dzzw.Dao;


import com.dzzw.Model.Employee;
import com.dzzw.utils.DButils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


//公务员
public class EmployeeDao {
    private final Connection con = DButils.getCon();
    //公务员登录
    public static List<Employee> EmployeeLogin(String employee_actnumber, String employee_password) {
        Employee employee = new Employee();
        List<Employee> list = new ArrayList<>();
        String sql = "select * from t_employee where employee_actnumber=? and employee_password=?";
        try {
            Connection con = DButils.getCon();
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setString(1,employee_actnumber);
            preparedStatement.setString(2,employee_password);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                employee.setEmployee_id(resultSet.getInt("employee_id"));
                employee.setEmployee_actnumber(resultSet.getString("employee_actnumber"));
                employee.setEmployee_password(resultSet.getString("employee_password"));
                employee.setEmployee_name(resultSet.getString("employee_name"));
                employee.setEmployee_entrytime(resultSet.getTime("employee_entrytime"));
                employee.setDepartment_id(resultSet.getInt("department_id"));
                employee.setLeader_id(resultSet.getInt("leader_id"));
                list.add(employee);
            }
            resultSet.close();
            preparedStatement.close();
            con.close();
        } catch(Exception e){
            System.out.println("数据查询失败！");

            e.printStackTrace();
        }
        return list;
    }

    //公务员账号信息添加
    public void EmployeeAdd(Employee employee){
        Date employee_entrytime = new Date();
        //设置日期格式化样式为：yyyy-MM-dd
        SimpleDateFormat SimpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");

        String sql = "insert into t_employee(employee_actnumber,employee_password,employee_name,employee_entrytime,department_id,leader_id) values(?,?,?,?,?,?)";
        try {
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setString(1,employee.getEmployee_actnumber());
            preparedStatement.setString(2,employee.getEmployee_password());
            preparedStatement.setString(3,employee.getEmployee_name());
            preparedStatement.setDate(4, java.sql.Date.valueOf(SimpleDateFormat.format(employee_entrytime.getTime())));//获取时间
            preparedStatement.setInt(5,employee.getDepartment_id());
            preparedStatement.setInt(6,employee.getLeader_id());
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        }catch (Exception e){
            System.out.println("数据添加失败！");

            e.printStackTrace();
        }
    }

    //公务员个人信息修改
    public void EmployeeModify(Employee employee){
        String sql = "update t_employee set employee_actnumber=?,employee_password=?,employee_name=?,department_id=?,leader_id=? where employee_id=?";
        try{
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setString(1,employee.getEmployee_actnumber());
            preparedStatement.setString(2,employee.getEmployee_password());
            preparedStatement.setString(3,employee.getEmployee_name());
            preparedStatement.setInt(4,employee.getDepartment_id());
            preparedStatement.setInt(5,employee.getLeader_id());
            preparedStatement.setInt(6,employee.getEmployee_id());
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        }catch (Exception e){
            System.out.println("数据修改失败！");

            e.printStackTrace();
        }
    }

    //公务员信息注销
    public void EmployeeDelete(int employee_id){
        String sql = "Delete from t_employee where employee_id = ?";
        try {
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1,employee_id);
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        }catch(Exception e){
            System.out.println("数据单条删除失败！");

            e.printStackTrace();
        }
    }

    //查询全部公务员信息
    public static List<Employee> SelectAllEmployee(){
        List<Employee> list = new ArrayList<>();
        String sql = "SELECT e.employee_id,e.employee_name,e.employee_actnumber,e.employee_password,e.employee_entrytime,l.leader_name,d.department_name FROM t_employee AS e INNER JOIN t_leader AS l ON e.leader_id = l.leader_id INNER JOIN t_department AS d ON e.department_id = d.department_id";
        try{
            Connection con =DButils.getCon();
            PreparedStatement preparedStatement =con.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Employee employee = new Employee();
                employee.setEmployee_id(resultSet.getInt(1));
                employee.setEmployee_actnumber(resultSet.getString(2));
                employee.setEmployee_password(resultSet.getString(3));
                employee.setEmployee_name(resultSet.getString(4));
                employee.setEmployee_entrytime(resultSet.getDate(5));
                employee.setLeader_name(resultSet.getString(6));
                employee.setDepartment_name(resultSet.getString(7));
                list.add(employee);
            }
            resultSet.close();
            preparedStatement.close();
            con.close();
            System.out.println("查询所有数据成功！");
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    //根据公务员id查询公务员信息
    public static List<Employee> SelectAllByEmployeeId(int employee_id){

        List<Employee> list = new ArrayList<>();
        String sql = "SELECT e.employee_id,e.employee_name,e.employee_actnumber,e.employee_password,e.employee_entrytime,l.leader_name,d.department_name FROM t_employee AS e INNER JOIN t_leader AS l ON e.leader_id = l.leader_id INNER JOIN t_department AS d ON e.department_id = d.department_id where e.employee_id = ?";
        try{
            Connection con =DButils.getCon();
            PreparedStatement preparedStatement =con.prepareStatement(sql);
            preparedStatement.setInt(1,employee_id);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Employee employee = new Employee();
                employee.setEmployee_id(resultSet.getInt(1));
                employee.setEmployee_actnumber(resultSet.getString(2));
                employee.setEmployee_password(resultSet.getString(3));
                employee.setEmployee_name(resultSet.getString(4));
                employee.setEmployee_entrytime(resultSet.getDate(5));
                employee.setLeader_name(resultSet.getString(6));
                employee.setDepartment_name(resultSet.getString(7));
                list.add(employee);
            }
            resultSet.close();
            preparedStatement.close();
            con.close();
            System.out.println("查询所有数据成功！");
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    //根据部门id查询下属公务员信息
    public static List<Employee> SelectAllByDepartmentId(int department_id){

        List<Employee> list = new ArrayList<>();
        String sql = "SELECT e.employee_id,e.employee_name,e.employee_actnumber,e.employee_password,e.employee_entrytime,l.leader_name,d.department_name FROM t_employee AS e INNER JOIN t_leader AS l ON e.leader_id = l.leader_id INNER JOIN t_department AS d ON e.department_id = d.department_id where e.department_id = ?";
        try{
            Connection con =DButils.getCon();
            PreparedStatement preparedStatement =con.prepareStatement(sql);
            preparedStatement.setInt(1,department_id);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Employee employee = new Employee();
                employee.setEmployee_id(resultSet.getInt(1));
                employee.setEmployee_actnumber(resultSet.getString(2));
                employee.setEmployee_password(resultSet.getString(3));
                employee.setEmployee_name(resultSet.getString(4));
                employee.setEmployee_entrytime(resultSet.getDate(5));
                employee.setLeader_name(resultSet.getString(6));
                employee.setDepartment_name(resultSet.getString(7));
                list.add(employee);
            }
            resultSet.close();
            preparedStatement.close();
            con.close();
            System.out.println("查询所有数据成功！");
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    //根据leader_id查询部门领导下属全部公务员
    public static List<Employee> SelectAllByLeaderId(int leader_id){

        List<Employee> list = new ArrayList<>();
        String sql = "SELECT e.employee_id,e.employee_name,e.employee_actnumber,e.employee_password,e.employee_entrytime,l.leader_name,d.department_name FROM t_employee AS e INNER JOIN t_leader AS l ON e.leader_id = l.leader_id INNER JOIN t_department AS d ON e.department_id = d.department_id where e.leader_id = ?";
        try{
            Connection con =DButils.getCon();
            PreparedStatement preparedStatement =con.prepareStatement(sql);
            preparedStatement.setInt(1,leader_id);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Employee employee = new Employee();
                employee.setEmployee_id(resultSet.getInt(1));
                employee.setEmployee_actnumber(resultSet.getString(2));
                employee.setEmployee_password(resultSet.getString(3));
                employee.setEmployee_name(resultSet.getString(4));
                employee.setEmployee_entrytime(resultSet.getDate(5));
                employee.setLeader_name(resultSet.getString(6));
                employee.setDepartment_name(resultSet.getString(7));
                list.add(employee);
            }
            resultSet.close();
            preparedStatement.close();
            con.close();
            System.out.println("查询所有数据成功！");
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }
}
